Context

column

The business situation

Coffee business is growing in US and also gaining momentum internationally. As a company, we would like to expand the business inside USA and in Europe to generate more revenue and acquire international markets. Now the revenue will be generated both in USD and Euros and hence a strategic planning is required to manage money in both the markets. A better understanding is required on exchange price and its impact from volatility. Determine the fluctuations of raw materials cost , correlation of weather and other markets like tea industries which could impact the supply and demand. Also, Manage coffee beans import effectively from variety of sources

The company allocates $10 million to manage receivables. The company wants us to:

The company wants to mitigate their risk by diversifying their export/import loads. This risk measures the amount of capital the company needs to maintain its portfolio of services.

Key questions

Your CFO has a few questions for us:

  1. How volatile the coffee business , given the daily market price of coffee bean? What is the nature of coffee returns? We want to reflect the ups and downs of price movements, something of prime interest to management. First, we calculate percentage changes as log returns. Our interest is in the ups and downs. To look at that we use if and else statements to define a new column called direction. We will build a data frame to house this analysis.

  2. How can we show the differences in the shape of ups and downs in coffee market, especially given our tolerance for risk? We can use the coffee.df data frame with ggplot2 and the cumulative relative frequency function stat_ecdf to begin to understand this data.

  3. How this expansion of the new business will impact the revenue and profits?

  4. Is there a correlation between tea and coffee market?

  5. How would we manage the allocation of existing resources given we have just landed in this new market?

  6. What is the nature of exchange rates in general and in particular for this data set? We want to reflect the ups and downs of rate movements, known to managers as currency appreciation and depreciation.

  7. How much capital we would need? Determine various measures of risk in the tail of distribution. Determine the risk capital we might need.

  8. Develop a model to optimize the holdings of each of the three money markets.

  9. Run scenarios to understand the range of the tangency portfolio (risky asset) as input to the collateral decision given a risk tolerance and a loss threshold.

    The risk tolerence and the loss threshold are given as follows

    Loss Threshold : -12% Risk Tolerence : 5%

    Loss should not exceed 12% no more than 5% of the time

  10. Including tangency portfolio, amount of cash and equivalents in the portfolio allocation, minimum risk portfolio and the risk and return characteristics of each commodity. In the interpretation, relate these results to the resource allocation decision and consequences for entering the the new market.

Key Q & A

Key business questions

  1. How volatile the coffee business , given the daily market price of coffee bean? What is the nature of coffee returns? We want to reflect the ups and downs of price movements, something of prime interest to management. First, we calculate percentage changes as log returns. Our interest is in the ups and downs. To look at that we use if and else statements to define a new column called direction. We will build a data frame to house this analysis.

    Figure 1.1 exhibits that the coffee Returns are more volatile in nature and often shows volatiality clustering. Also, there are peak negative returns occured in 1991 and in 2000 which are all time peak returns. This suggests that high negative returns are likely to occur than the postitive returns.

    As shown in the above table, zero value for the median indicates that both postive and negative returns occur equal number of times (i.e) the probability of negative return is 0.5. Mean value of all the returns is nearly zero shows that our net returns is zero with o profit gain in the market. Skewness of 6.979 is due to the more mass in the left tail and the kurtosis of 38 is huge showing that the tail is very thick.

    Heavy-tailed distributions (distribution with high kurtosis) are important models in finance, because equity returns and other changes in market prices usually have heavy tails. In finance applications, one is especially concerned when the return distribution has heavy tails because of the possibility of an extremely large negative return, which could, for example, entirely deplete the capital reserves of a firm.

  2. How can we show the differences in the shape of ups and downs in coffee market, especially given our tolerance for risk? We can use the coffee.df data frame with ggplot2 and the cumulative relative frequency function stat_ecdf to begin to understand this data.

    The Figure 1.4 shows, that the span of negative curve is long ranging from 0 to -8 whereas the span of positive curve is short ranging from 0 to 5. The tolerance rate for this curve is 3.19 , anything below this value is ok but anything above 3.19 is not tolerable

  3. How this expansion of the new business will impact the revenue and profits?

    The performance of these commodities will have huge an impact on size and timing of currency transactions overseas because of the stylized facts of money markets. It is important to schedule these transactions after studying the ups and downs in the market to gain maximum benefits in return

  4. Is there a correlation between tea and coffee market?

    Figure 3.1 shows that coffee and Tea markets are highly correlation and the changes in one market affect the other market even with lag of 15 days.

  5. How would we manage the allocation of existing resources given we have just landed in this new market?

    In the new market we need to satisfy the demands and customer needs quickly to gain the positive feedback from the new customers and hence most of the resource will be allocated to focus and satisfy new customer demands.We might face resource shortage in meeting current customer demands.

  6. What is the nature of exchange rates in general and in particular for this data set? We want to reflect the ups and downs of rate movements, known to managers as currency appreciation and depreciation.

    Figure 2.1 on “exchange rate percent changes” for different money market shows that they are extremely volatile in nature with volatility clustering. You can see that the following extreme volatilities occurred in each of the money market. Volatilities and correlation behavior of one market with other changes by year and it is not consistent from one year to another

  7. How much capital we would need? Determine various measures of risk in the tail of distribution. Determine the risk capital we might need.

    Expected shortfall is $13.46 M VaR is $10.2 M The coffee company already had $10 M for managing receivebales and needs and additional $3.46 M as a risk capital to handle extreme events

  8. Develop a model to optimize the holdings of each of the three currencies.

    Tangency portfolio is charted shown in figure 6.1 to find the optimum return per risk using sharpes ratio for the three money market combination

  9. Run scenarios to understand the range of the tangency portfolio (risky asset) as input to the collateral decision given a risk tolerance and a loss threshold.

    The risk tolerence and the loss threshold are given as follows

    Loss Threshold : -12% Risk Tolerence : 5%

    Loss should not exceed 12% no more than 5% of the time

  10. Including tangency portfolio, amount of cash and equivalents in the portfolio allocation, minimum risk portfolio and the risk and return characteristics of each money market. In the interpretation, relate these results to the resource allocation decision and consequences for entering the the new market.

    Maixumum Tangeny portfolio allocation

    The portfolio allocation for optimised return and the weights of different markets are given below

    EUR : 0.54128 CAD : -0.02129 CHF : 0.48001

    Given the above weights, we are taking long position on EUR and short position on CAD.

    Minimum Tangeny portfolio allocation

    The portfolio allocation for 0 % risk and the weights of different markets are given below

    EUR : 0.4153 Nickel : 0.2037 Aluminium : 0.3810

Data exploration on Commodities

Column

Descriptive Stats : Commodity - Coffee 

'data.frame':   580 obs. of  5 variables:
 $ date     : Date, format: "2019-08-09" "2019-08-08" ...
 $ price    : num  97.3 97.4 96.9 97 95.7 ...
 $ return   : num  0.826 0.103 -0.515 0.155 -1.453 ...
 $ size     : num  0.826 0.103 0.515 0.155 1.453 ...
 $ direction: Factor w/ 3 levels "down","same",..: 3 3 1 3 1 3 1 3 1 3 ...
      date                price           return             size     
 Min.   :2017-01-03   Min.   : 86.7   Min.   :-8.2852   Min.   :0.00  
 1st Qu.:2017-08-03   1st Qu.:103.5   1st Qu.:-0.9413   1st Qu.:0.51  
 Median :2018-03-26   Median :119.5   Median : 0.1573   Median :1.12  
 Mean   :2018-04-09   Mean   :119.2   Mean   : 0.0858   Mean   :1.42  
 3rd Qu.:2018-12-17   3rd Qu.:130.7   3rd Qu.: 1.2801   3rd Qu.:1.89  
 Max.   :2019-08-09   Max.   :174.3   Max.   : 6.4002   Max.   :8.29  
 direction 
 down:268  
 same:  4  
 up  :308
mean median std_dev IQR skewness kurtosis
PCOFFROBUSDM 0.1221 -0.5841 6.4569 7.0588 0.8356 6.9792
PTEAUSDM 0.0054 0.0000 7.0955 9.2018 -0.0888 3.6501 
[1] "Coffee Movements"
direction return.avg return.sd quantile.5 quantile.95 percent
down -4.2049 3.6564 -10.60897 -0.32427 54.366
up 5.2770 5.1520 0.38905 13.58307 45.634 
[1] "Tea Movements"
direction return.avg return.sd quantile.5 quantile.95 percent
down -5.5037 4.5639 -15.90574 -0.28618 49.8591
same 0.0000 0.0000 0.00000 0.00000 1.1268
up 5.6095 4.3808 0.82012 14.71337 49.0141 
'data.frame':   355 obs. of  5 variables:
 $ date     : Date, format: "1990-02-01" "1990-03-01" ...
 $ price    : num  51.3 57.7 57.4 55 52.4 ...
 $ return   : num  1.532 11.722 -0.504 -4.199 -4.935 ...
 $ size     : num  1.532 11.722 0.504 4.199 4.935 ...
 $ direction: Factor w/ 2 levels "down","up": 2 2 1 1 1 1 2 2 2 1 ...
    shape       rate  
  1.125680   0.213318 
 (0.111266) (0.026346)
      m          s          df   
  -3.53061    2.66204    4.17291 
 ( 0.26229) ( 0.23833) ( 1.28724)
    shape     rate  
  1.27227   0.30257 
 (0.11640) (0.03375)

Column

Figure 1.1: Stylized Facts - Return

Figure 1.2: Stylized Facts - Size

Figure 1.3: Stylized Facts - Return and Size

Column

Figure 1.4: Coffee Tolerable Rate

Data exploration On Currencies

Column

Descriptive Statistics : Currencies 

       Date    EUR    CAD    CHF
1 8/12/2019 1.1179 1.3235 0.9735
3  8/9/2019 1.1200 1.3222 0.9726
4  8/8/2019 1.1180 1.3227 0.9748
5  8/7/2019 1.1199 1.3303 0.9754
6  8/6/2019 1.1199 1.3281 0.9764
7  8/5/2019 1.1203 1.3204 0.9735
         Date    EUR    CAD    CHF
810 1/10/2017 1.0554 1.3232 1.0169
811  1/9/2017 1.0574 1.3216 1.0151
813  1/6/2017 1.0532 1.3237 1.0172
814  1/5/2017 1.0607 1.3225 1.0098
815  1/4/2017 1.0489 1.3302 1.0212
816  1/3/2017 1.0405 1.3427 1.0273
'data.frame':   581 obs. of  4 variables:
 $ Date: Factor w/ 581 levels "1/10/2017","1/10/2018",..: 505 547 544 542 539 537 517 501 490 487 ...
 $ EUR : num  1.12 1.12 1.12 1.12 1.12 ...
 $ CAD : num  1.32 1.32 1.32 1.33 1.33 ...
 $ CHF : num  0.974 0.973 0.975 0.975 0.976 ...
        Date          EUR            CAD            CHF       
 1/10/2017:  1   Min.   :1.04   Min.   :1.21   Min.   :0.926  
 1/10/2018:  1   1st Qu.:1.12   1st Qu.:1.28   1st Qu.:0.973  
 1/10/2019:  1   Median :1.14   Median :1.31   Median :0.991  
 1/11/2017:  1   Mean   :1.15   Mean   :1.31   Mean   :0.986  
 1/11/2019:  1   3rd Qu.:1.18   3rd Qu.:1.33   3rd Qu.:0.999  
 1/12/2017:  1   Max.   :1.25   Max.   :1.37   Max.   :1.027  
 (Other)  :575                                                
'data.frame':   580 obs. of  10 variables:
 $ dates        : Date, format: "2019-08-09" "2019-08-08" ...
 $ returns.EUR  : num  0.1877 -0.1787 0.1698 0 0.0357 ...
 $ returns.CAD  : num  -0.0983 0.0378 0.5729 -0.1655 -0.5815 ...
 $ returns.CHF  : num  -0.0925 0.2259 0.0615 0.1025 -0.2975 ...
 $ size.EUR     : num  0.1877 0.1787 0.1698 0 0.0357 ...
 $ size.CAD     : num  0.0983 0.0378 0.5729 0.1655 0.5815 ...
 $ size.CHF     : num  0.0925 0.2259 0.0615 0.1025 0.2975 ...
 $ direction.EUR: num  1 -1 1 0 1 -1 -1 -1 1 -1 ...
 $ direction.CAD: num  -1 1 1 -1 -1 1 1 -1 -1 1 ...
 $ direction.CHF: num  -1 1 1 1 -1 1 1 1 -1 1 ...
'zooreg' series from 2017-01-03 to 2019-08-09
  Data: num [1:580, 1:9] -0.804 -1.119 0.71 -0.398 0.189 ...
 - attr(*, "dimnames")=List of 2
  ..$ : NULL
  ..$ : chr [1:9] "EUR" "CAD" "CHF" "EUR" ...
  Index:  Date[1:580], format: "2017-01-03" "2017-01-04" "2017-01-05" "2017-01-06" "2017-01-09" ...
  Frequency: 1 
                EUR       CAD      CHF     EUR      CAD     CHF EUR CAD
2017-01-03 -0.80406  0.935321  0.59556 0.80406 0.935321 0.59556  -1   1
2017-01-04 -1.11871  0.580542  1.12261 1.11871 0.580542 1.12261  -1   1
2017-01-05  0.70959 -0.090696 -0.73015 0.70959 0.090696 0.73015   1  -1
2017-01-06 -0.39799  0.158772  0.20666 0.39799 0.158772 0.20666  -1   1
2017-01-09  0.18932 -0.120992 -0.17717 0.18932 0.120992 0.17717   1  -1
2017-01-10 -0.26495  0.401348  0.27573 0.26495 0.401348 0.27573  -1   1
           CHF
2017-01-03   1
2017-01-04   1
2017-01-05  -1
2017-01-06   1
2017-01-09  -1
2017-01-10   1

Column

Figure 2.1:Exchange Rate Percent Changes

Column

Figure 2.2:Tolerable Rate

Market relationships : Commodities

Column

Figure 3.1: Correlation Analysis - Coffee and Tea Markets

Column

Figure 3.2: Correlation Analysis - Quantile Regression

Market relationships : Currencies

Column

Figure 4.1: Correlation Analysis between currencies

mean median std_dev IQR skewness kurtosis
EUR 0.359 0.284 0.284 0.339 1.358 5.96
CAD 0.344 0.270 0.281 0.356 1.260 4.79
CHF 0.329 0.262 0.279 0.370 1.226 4.42 
[1] 0.0092744

Figure 4.2: Quantile Regression

Column

Figure 4.3: Year over Year Volatilities

Figure 4.4: Correlations behind and after

mean median std_dev IQR skewness kurtosis
ahead_x 0.0029 -0.015 0.4454 0.5337 0.1608 3.5535
behind_y 0.0076 -0.010 0.4288 0.5234 -0.0035 3.6058
mean median std_dev IQR skewness kurtosis
ahead_y 0.0094 -0.010 0.4330 0.5291 0.0103 3.5670
behind_x 0.0000 -0.015 0.4443 0.5340 0.1615 3.5633 
[1] -0.054206
[1] 0.054227

Market risk of loss

Column

Figure 5.1: Expected Shortfall and Value at Risk

Column

Figure 5.2: Loss Analysis - Histogram

Column

Figure 5.3: Mean excess plot to determine thresholds for extreme event management

Portfolio Analytics on Currencies

Column

Figure 6.1: Tangency Portfolio

         mean    median std_dev    IQR skewness kurtosis
EUR -0.012371 -0.008785  0.4575 0.5750 -0.19789    3.671
CAD  0.002483 -0.014998  0.4449 0.5326  0.16249    3.551
CHF  0.009274 -0.009876  0.4314 0.5238  0.01049    3.588

Column

Figure 6.2: Tangency portfolio sampled mean simulation

Column

Figure 6.3: Mean Vs Standard Deviation of portfolio return

[1]  0.17000  0.87584 -0.04584
[1] 0.444 0.159 0.397
[1]  0.17000  0.87584 -0.04584
[1] 0.444 0.159 0.397